package com.cls.system.service.impl;

import cn.hutool.core.lang.Validator;
import cn.hutool.core.util.IdcardUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.cls.common.entity.QueryRequest;
import com.cls.common.enums.DictEnum;
import com.cls.common.exception.MyException;
import com.cls.common.utils.DESUtil;
import com.cls.common.utils.FileUtil;
import com.cls.system.entity.Expert;
import com.cls.system.entity.SysDict;
import com.cls.system.mapper.ExpertMapper;
import com.cls.system.service.IExpertService;
import com.cls.system.service.ISysDictService;
import com.cls.system.service.IUserRoleService;
import com.cls.system.service.IUserService;
import com.github.pagehelper.PageHelper;
import org.apache.commons.lang3.StringUtils;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Caching;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.annotation.Propagation;
import lombok.RequiredArgsConstructor;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.github.pagehelper.PageInfo;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 专家表 Service实现
 *
 * @author Carey
 * @date 2020-11-06 10:47:30
 */
@Service
@RequiredArgsConstructor
@Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
public class ExpertServiceImpl extends ServiceImpl<ExpertMapper, Expert> implements IExpertService {
    private final ISysDictService sysDictService;
    private final IUserService iUserService;


    /**
     * 人员导入
     *
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class, isolation = Isolation.READ_UNCOMMITTED)
    public void uploadExpert(InputStream inputStream) {
        // 2.应用HUtool ExcelUtil获取ExcelReader指定输入流和sheet
        ExcelReader excelReader = ExcelUtil.getReader(inputStream, 0);
        // 3.读取第二行到最后一行数据
//            List<List<Object>> employeeExcelData = excelReader.read(1, excelReader.getRowCount());
        //读取数据到map
        List<Map<String, Object>> employeeExcelData = excelReader.readAll();

        if(employeeExcelData==null||employeeExcelData.size()==0){
            throw new MyException("模板中专家没有数据");
        }

        List<Expert> expertExcelList = new ArrayList<>();
        //判断模板是否正确 headerAlias表头
        List<Object> headerAlias = excelReader.readRow(0);
        if(headerAlias==null||headerAlias.size()==0){
            throw new MyException("模板存在问题，请重新下载模板");
        }
        List<String> userAlias=new ArrayList<>();
        userAlias.add("*姓名");
        userAlias.add("*工作单位");
        userAlias.add("*职称");
        userAlias.add("*证件类型");
        userAlias.add("*证件号");
        userAlias.add("*学科分类");
        userAlias.add("*理论与实务专家");
        userAlias.add("*京内与京外专家");
        userAlias.add("银行帐号");
        userAlias.add("银行帐号开户行");
        userAlias.add("固定电话");
        userAlias.add("*移动手机");
        userAlias.add("地址");
        userAlias.add("*邮箱");
        userAlias.add("*学术委员会成员");
       //验证模板是否正确1
        userAlias.forEach(row->{
            if(row==null){
                throw new MyException("模板存在问题，请重新下载模板");
            }
            Boolean isTureTm=false;
            for (Object item:headerAlias){
                if(row.equals(item)){
                    isTureTm=true;
                    break;
                }else {
                    isTureTm=false;
                }
            }
            if(!isTureTm) {
                throw new MyException("模板存在问题，请重新下载模板");
            }
        });
        //验证模板是否正确2
        headerAlias.forEach(row->{
            if(row==null){
                throw new MyException("模板存在问题，请重新下载模板");
            }
            Boolean isTureTm=false;
            for (Object item:userAlias){
                if(row.equals(item)){
                    isTureTm=true;
                    break;
                }else {
                    isTureTm=false;
                }
            }
            if(!isTureTm) {
                throw new MyException("模板存在问题，请重新下载模板");
            }
        });

        employeeExcelData.forEach(row -> {
            String name = validatedExcelData(row, "*姓名",true);
            String company = validatedExcelData(row, "*工作单位",true);
            String titleTypeCode = validatedExcelData(row, "*职称",true);
            String identityTypeCode = validatedExcelData(row, "*证件类型",true);
            String identityId = validatedExcelData(row, "*证件号",true);
            String subjectTypeCode = validatedExcelData(row, "*学科分类",true);
            String featureTypeCode = validatedExcelData(row, "*理论与实务专家",true);
            String districtTypeCode = validatedExcelData(row, "*京内与京外专家",true);
            String accountId = validatedExcelData(row, "银行帐号",false);
            String bankName = validatedExcelData(row, "银行帐号开户行",false);
            String phone = validatedExcelData(row, "固定电话",false);
            String mobile = validatedExcelData(row, "*移动手机",true);
            String address = validatedExcelData(row, "地址",false);
            String email = validatedExcelData(row, "*邮箱",true);
            String isMember = validatedExcelData(row, "*学术委员会成员",true);

            if (!Validator.isMobile(mobile)) {
                throw new MyException("手机号码:" + mobile+"不合法");
            }
            if(identityTypeCode.equals(DictEnum.SFZ.getName())){
                if (!IdcardUtil.isValidCard(identityId)) {
                    throw new MyException("证件号:" + identityId+"不合法");
                }
            }
            if (!checkBankCard(accountId)&&StringUtils.isNotBlank(accountId)) {
                throw new MyException("银行卡号:" + accountId+"不合法");
            }
            if (!isPhone(phone)&&StringUtils.isNotBlank(phone)) {
                throw new MyException("固定电话:" + phone+"不合法");
            }
            //证件类型
            List<SysDict> zjlxTypeList = sysDictService.findByParentCode(DictEnum.ZJLX.getCode());
            boolean zjlxIs=false;
            for (SysDict sub:zjlxTypeList) {
                if(sub.getName().equals(identityTypeCode)){
                    identityTypeCode=sub.getCode();
                    zjlxIs=true;
                }
            }
            if(!zjlxIs){
                throw new MyException("证件类型名称:" + identityTypeCode+"不合法");
            }
            //学科分类
            List<SysDict> subjectTypeList = sysDictService.findByParentCode(DictEnum.SUBJECT_TYPE.getCode());
            boolean subjectIs=false;
            for (SysDict sub:subjectTypeList) {
               if(sub.getName().equals(subjectTypeCode)){
                   subjectTypeCode=sub.getCode();
                   subjectIs=true;
               }
            }
            if(!subjectIs){
                throw new MyException("学科分类名称:" + subjectTypeCode+"不合法");
            }
            //理论与实务专家
            List<SysDict> featureList = sysDictService.findByParentCode(DictEnum.FEATURE.getCode());
            boolean featureIs=false;
            for (SysDict feature:featureList) {
                if(feature.getName().equals(featureTypeCode)){
                    featureTypeCode=feature.getCode();
                    featureIs=true;
                }
            }
            if(!featureIs){
                throw new MyException("理论与实务专家名称:" + featureTypeCode+"不合法");
            }

            //京内与京外专家
            List<SysDict> districtList = sysDictService.findByParentCode(DictEnum.DISTRICT.getCode());
            boolean districtIs=false;
            for (SysDict district:districtList) {
                if(district.getName().equals(districtTypeCode)){
                    districtTypeCode=district.getCode();
                    districtIs=true;
                }
            }
            if(!districtIs){
                throw new MyException("京内与京外专家名称:" + districtTypeCode+"不合法");
            }
            Expert expert = new Expert();
                expert.setIsMember(false);
            if("是".equals(isMember)){
                expert.setIsMember(true);
            }
            expert.setName(name);
            expert.setIsPublish(false);
            expert.setCompany(company);
            expert.setTitleTypeCode(titleTypeCode);
            expert.setIdentityTypeCode(identityTypeCode);
            expert.setIdentityId(identityId);
            expert.setSubjectTypeCode(subjectTypeCode);
            expert.setFeatureTypeCode(featureTypeCode);
            expert.setDistrictTypeCode(districtTypeCode);
            expert.setAccountId(accountId);
            expert.setBankName(bankName);
            expert.setPhone(phone);
            expert.setMobile(mobile);
            expert.setAddress(address);
            expert.setEmail(email);
            expertExcelList.add(expert);

        });
        Set<Expert> set = new TreeSet<Expert>(new Comparator<Expert>() {
            public int compare(Expert a, Expert b) {
                // 字符串则按照asicc码升序排列
                return a.getMobile().compareTo(b.getMobile());
            }
        });
        set.addAll(expertExcelList);
        if (set.size() < expertExcelList.size()) {
            throw new MyException("模板中移动手机存在重复");
        }

       expertExcelList.forEach(expert ->
               this.saveExpert(expert)
       );
    }
    private void saveExpert(Expert expert) {
        //获取字段值
        Integer mobileNum=this.baseMapper.findByMobile(expert.getMobile());
        Integer idCardNum=this.baseMapper.findByIdCard(expert.getIdentityId());

        if(mobileNum>0){
            throw new MyException("模板中移动手机号:"+expert.getMobile()+"数据库中已存在");
        }
        if(idCardNum>0){
            throw new MyException("模板中证件号:"+expert.getIdentityId()+"数据库中已存在");
        }

        this.save(expert);

    }
    private String validatedExcelData(Map<String, Object> rowData, String filedName, boolean isRequest) {
        //获取字段值
        String filedValue = String.valueOf(rowData.get(filedName));
        Optional<String> validatedValueOptional = Optional.ofNullable(filedValue)
                .map(str -> StringUtils.isBlank(filedValue) ? null : filedValue);
        //判断是否是必填
        if (isRequest) {
            validatedValueOptional.orElseThrow(() -> new MyException(filedName+",这列不能有空值，请仔细检查"));
        } else if (StringUtils.isEmpty(filedValue) || "null".equals(filedValue)) {
            return "";
        }
        return validatedValueOptional.get();
    }
    @Override
    public PageInfo<Expert> findExperts(QueryRequest request, Expert expert) {
      /*  LambdaQueryWrapper<Expert> queryWrapper = new LambdaQueryWrapper<>();*/

        PageHelper.startPage(request.getPageNum(), request.getPageSize());
        List<Expert> list= this.baseMapper.findExpertDetail(expert);
        list.forEach(e->{
              e.setExistUser(false);
            if(e.getUserId()!=null){
              e.setExistUser(true);
            }
        });
        PageInfo pageInfo = new PageInfo(list);
        return pageInfo;
    }

    @Override
    public List<Expert> findExperts(Expert expert) {
	    LambdaQueryWrapper<Expert> queryWrapper = new LambdaQueryWrapper<>();
		// TODO 设置查询条件
        if(expert.getIsPublish() != null){
            queryWrapper.eq(Expert::getIsPublish, expert.getIsPublish());
        }
        if(StringUtils.isNotBlank(expert.getName())){
            queryWrapper.and(wrapper ->
                            wrapper.like(Expert::getName,expert.getName()).or().like(Expert::getCompany,expert.getName())
        );
        }
        if(StringUtils.isNotBlank(expert.getSubjectTypeCode())){
            queryWrapper.eq(Expert::getSubjectTypeCode,expert.getSubjectTypeCode());
        }
        if(StringUtils.isNotBlank(expert.getMobile())){
            queryWrapper.eq(Expert::getMobile, expert.getMobile());
        }
        if(StringUtils.isNotBlank(expert.getIdentityId())){
            queryWrapper.eq(Expert::getIdentityId, expert.getIdentityId());
        }
		return this.baseMapper.selectList(queryWrapper);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void createExpert(Expert expert) {
        this.save(expert);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void updateExpert(Expert expert) {
        this.saveOrUpdate(expert);
    }
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteExpert(Expert expert) {
        LambdaQueryWrapper<Expert> wrapper = new LambdaQueryWrapper<>();
	    // TODO 设置删除条件
	    this.remove(wrapper);
	}

    @Override
    public Expert findById(Long id) {
        return this.baseMapper.selectById(id);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteExperts(String[] ids) {
        List<String> list = Arrays.asList(ids);
        //删除所有专家账号
        list.forEach(item->{
            Long expertId = Long.valueOf(item);
            Expert expert = this.baseMapper.selectById(expertId);
            if(expert.getUserId()!=null){
                //删除专家user对象和权限role
                String[] userId=new String[]{expert.getUserId().toString()};
                iUserService.deleteUsers(userId);
            }
        });
        // 删除专家
        this.removeByIds(list);

    }
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void pubishExperts(String[] ids) {
        List<String> list = Arrays.asList(ids);
        // 发布专家
        for (String id:list) {
            Long expertId= Long.valueOf(id);
            Expert expert =new Expert();
            expert.setExpertId(expertId);
            expert.setIsPublish(true);
            this.saveOrUpdate(expert);
        }


    }
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void cancelExperts(String[] ids) {
        List<String> list = Arrays.asList(ids);
        // 取消发布专家
        for (String id:list) {
            Long expertId= Long.valueOf(id);
            Expert expert =new Expert();
            expert.setExpertId(expertId);
            expert.setIsPublish(false);
            this.saveOrUpdate(expert);
        }
    }

    @Override
    public PageInfo<Expert> findByIds(QueryRequest request, Set<Long> expertIds) {
        LambdaQueryWrapper<Expert> queryWrapper = new LambdaQueryWrapper<>();
        if(expertIds != null){
            queryWrapper.in(Expert::getExpertId, expertIds);
        }
        PageHelper.startPage(request.getPageNum(), request.getPageSize());
        List<Expert> list= list(queryWrapper);
        PageInfo pageInfo = new PageInfo(list);
        return pageInfo;

    }

    @Override
    public List<Expert> findByIds(Set<Long> expertIds) {
        LambdaQueryWrapper<Expert> queryWrapper = new LambdaQueryWrapper<>();
        if(expertIds != null){
            queryWrapper.in(Expert::getExpertId, expertIds);
        }
        List<Expert> experts = list(queryWrapper);
        for(Expert one:experts){
            SysDict sysDict = sysDictService.findByCode(one.getPositionTypeCode());
            if(sysDict != null){
                one.setPositionTypeName(sysDict.getName());
            }
        }
        return experts;

    }
    @Override
    public Integer findByMobile(String mobile) {
        return this.baseMapper.findByMobile(mobile);
    }
    @Override
    public Integer findByIdCard(String identityId) {
        return this.baseMapper.findByIdCard(identityId);
    }
    @Override
    public Expert findByUserId(Long userId) {
        return this.baseMapper.findByUserId(userId);
    }
    @Override
    public void download(List<Expert> experts, HttpServletResponse response) throws IOException {
        List<Map<String, Object>> list = new ArrayList<>();
        for (int i=0;i<experts.size();i++) {
            Expert expert = experts.get(i);
            Map<String, Object> map = new LinkedHashMap<>();
            map.put("序号", i+1);
            map.put("姓名", expert.getName());
            map.put("工作单位", expert.getCompany());
            map.put("职称",expert.getTitleTypeCode());
            if (StringUtils.isNotBlank(expert.getIdentityTypeCode())) {
                SysDict dict = sysDictService.findByCode(expert.getIdentityTypeCode());
                map.put("证件类型", dict == null ? "" : dict.getName());
            }
            map.put("证件号", expert.getIdentityId());
            map.put("学科分类", "");
            if (StringUtils.isNotBlank(expert.getSubjectTypeCode())) {
                SysDict dict = sysDictService.findByCode(expert.getSubjectTypeCode());
                map.put("学科分类", dict == null ? "" : dict.getName());
            }
            map.put("理论与实务专家", "");
            if (StringUtils.isNotBlank(expert.getFeatureTypeCode())) {
                SysDict dict = sysDictService.findByCode(expert.getFeatureTypeCode());
                map.put("理论与实务专家", dict == null ? "" : dict.getName());
            }
            map.put("京内与京外专家", "");
            if (StringUtils.isNotBlank(expert.getDistrictTypeCode())) {
                SysDict dict = sysDictService.findByCode(expert.getDistrictTypeCode());
                map.put("京内与京外专家", dict == null ? "" : dict.getName());
            }
            map.put("银行帐号", expert.getAccountId());
            map.put("银行帐号开户行", expert.getBankName());
            map.put("固定电话", expert.getPhone());
            map.put("移动手机", expert.getMobile());
            map.put("地址", expert.getAddress());
            map.put("邮箱", expert.getEmail());
            map.put("是否发布", expert.getIsPublish() ? "已发布" : "未发布");
            map.put("学术委员会成员", expert.getIsMember() ? "是" : "否");
            list.add(map);
        }
        FileUtil.downloadExcel(list, response);
    }
   //验证银行卡
    public static boolean checkBankCard(String bankCard) {
                      if(bankCard.length() < 15 || bankCard.length() > 19) {
                           return false;
                        }
                     char bit = getBankCardCheckCode(bankCard.substring(0, bankCard.length() - 1));
                       if(bit == 'N'){
                             return false;
                       }
                       return bankCard.charAt(bankCard.length() - 1) == bit;
              }

    public static char getBankCardCheckCode(String nonCheckCodeBankCard){
                  if(nonCheckCodeBankCard == null || nonCheckCodeBankCard.trim().length() == 0
                           || !nonCheckCodeBankCard.matches("\\d+")) {
                         //如果传的不是数据返回N
                         return 'N';
                    }
                  char[] chs = nonCheckCodeBankCard.trim().toCharArray();
                  int luhmSum = 0;
                for(int i = chs.length - 1, j = 0; i >= 0; i--, j++) {
                     int k = chs[i] - '0';
                         if(j % 2 == 0) {
                               k *= 2;
                                k = k / 10 + k % 10;
                         }
                        luhmSum += k;
                     }
                  return (luhmSum % 10 == 0) ? '0' : (char)((10 - luhmSum % 10) + '0');
    }
    //验证电话
    public static boolean isPhone(String str) {
        Pattern p1 = null;
        Matcher m = null;
        boolean b = false;
        p1 = Pattern.compile("^[0][1-9]{2,3}-[0-9]{5,10}$");  // 验证带区号的
        m = p1.matcher(str);
        b = m.matches();

        return b;
    }
}
